#!/usr/bin/env python
# coding: utf-8

# In[ ]:


from flask import Flask, render_template, request, redirect, url_for, flash, jsonify
from flask_login import UserMixin, LoginManager, login_user, login_required, logout_user, current_user
from werkzeug.security import generate_password_hash, check_password_hash
from flask import request
from functools import wraps
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import event
from datetime import datetime
from sqlalchemy import asc, desc, or_
from werkzeug.utils import secure_filename
#from flask_migrate import Migrate
import os


# In[ ]:


# 初始化 Flask 应用
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///inventory.db'
app.secret_key = os.environ.get('SECRET_KEY', 'optional_default_value')  # 使用环境变量保持安全会话
app.config['UPLOAD_FOLDER'] = 'static/uploads'  # 配置文件上传目录
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif'}
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['REGISTER_ENABLED'] = True  # 默认开启注册功能


# In[ ]:


# 初始化数据库
db = SQLAlchemy(app)
# 初始化 Flask-Migrate
#migrate = Migrate(app, db)


# In[ ]:


# 定义数据库模型
class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    quantity = db.Column(db.Integer, default=0)
    price = db.Column(db.Float, nullable=False)
    unit = db.Column(db.String(50), nullable=False)  # 添加单位字段
    image_url = db.Column(db.String(255), nullable=True)  # 添加图片URL字段

class Sale(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    cash_id = db.Column(db.Integer, db.ForeignKey('cash.id'), nullable=False)  # 关联库存现金记录
    customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'), nullable=True)  # 关联客户
    quantity = db.Column(db.Integer, nullable=False)
    sale_date = db.Column(db.DateTime, nullable=False)
    sale_price = db.Column(db.Float, nullable=False) 
    salesperson = db.Column(db.String(100), nullable=True)  # 添加销售人字段
    discount = db.Column(db.Float, nullable=False, default=1.0)  # 销售时的折扣
    notes = db.Column(db.String(200), nullable=True)  # 添加备注字段
    payment_method = db.Column(db.String(50), nullable=False, default='cash')  # 支付方式：cash（现金）或 prepaid（预存金额）
    product = db.relationship('Product', backref=db.backref('sales', lazy=True))
    cash = db.relationship('Cash', backref=db.backref('sale', uselist=False))
    customer = db.relationship('Customer', backref=db.backref('sales', lazy=True))  # 关联客户


class Purchase(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    cash_id = db.Column(db.Integer, db.ForeignKey('cash.id'), nullable=True)  # 关联库存现金记录
    supplier_id = db.Column(db.Integer, db.ForeignKey('supplier.id'), nullable=True)  # 关联供应商记录
    quantity = db.Column(db.Integer, nullable=False)
    purchase_date = db.Column(db.DateTime, nullable=False)
    purchase_price = db.Column(db.Float, nullable=False)  # 添加采购单价字段
    purchase_person = db.Column(db.String(100), nullable=True)  # 添加采购人字段
    discount = db.Column(db.Float, nullable=False, default=1.0)  # 采购时的折扣
    notes = db.Column(db.String(200), nullable=True)  # 添加备注字段
    product = db.relationship('Product', backref=db.backref('purchases', lazy=True))# 添加关系，引用 Product 模型
    cash = db.relationship('Cash', backref=db.backref('purchase', uselist=False))

class Cash(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    amount = db.Column(db.Float, nullable=False)  # 金额
    transaction_type = db.Column(db.String(50), nullable=False)  # 交易类型，"income" 或 "expense"
    category = db.Column(db.String(50), nullable=False)  # 交易分类，例如 "销售"、"采购"、"其他收入"、"其他支出"
    description = db.Column(db.String(200), nullable=True)  # 交易描述
    transaction_date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)  # 交易日期
    balance = db.Column(db.Float, nullable=False)  # 交易后的库存现金余额

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), unique=True, nullable=False)
    password = db.Column(db.String(100), nullable=False)
    is_active = db.Column(db.Boolean, default=True)  # 用户是否激活
    role = db.Column(db.String(50), nullable=False, default='user')


# 定义客户模型
class Customer(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)  # 客户名称
    prepaid_amount = db.Column(db.Float, default=0.0)  # 客户账户预存金额
    account_manager = db.Column(db.String(100), nullable=True)  # 开户人
    created_at = db.Column(db.DateTime, default=datetime.utcnow)  # 创建时间
    allow_overdraft = db.Column(db.Boolean, default=False)  # 是否允许透支

# 定义客户存入金额明细模型
class Deposit(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'), nullable=False)
    amount = db.Column(db.Float, nullable=False)  # 存入金额
    deposit_date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)  # 存入时间
    description = db.Column(db.String(200), nullable=True)  # 存入描述
    customer = db.relationship('Customer', backref=db.backref('deposits', lazy=True))


# 定义供应商模型
class Supplier(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)  # 供应商名称
    prepaid_amount = db.Column(db.Float, default=0.0)  # 供应商账户预存金额
    contact_person = db.Column(db.String(100), nullable=True)  # 联系人
    contact_phone = db.Column(db.String(20), nullable=True)  # 联系电话
    address = db.Column(db.String(200), nullable=True)  # 地址
    created_at = db.Column(db.DateTime, default=datetime.utcnow)  # 创建时间
    
# 定义供应商存入金额明细模型
class SupplierDeposit(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    supplier_id = db.Column(db.Integer, db.ForeignKey('supplier.id'), nullable=False)
    amount = db.Column(db.Float, nullable=False)  # 存入金额
    deposit_date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)  # 存入时间
    description = db.Column(db.String(200), nullable=True)  # 存入描述
    supplier = db.relationship('Supplier', backref=db.backref('deposits', lazy=True))
    


# 创建数据库和表
with app.app_context():
    db.create_all()


# In[ ]:


login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'  # 登录视图的 endpoint

@login_manager.unauthorized_handler
def unauthorized():
    # 不显示任何消息，直接重定向到登录页面
    return redirect(url_for('login'))


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        user = User.query.filter_by(username=username).first()
        if user and check_password_hash(user.password, password):
            login_user(user)
            return redirect(url_for('index'))
        else:
            flash('用户名或密码错误！', 'error')
    return render_template('login.html')

@login_manager.user_loader
def load_user(user_id):
        return db.session.get(User, int(user_id)) #User.query.get(int(user_id))
    
@app.route('/logout')
@login_required
def logout():
    logout_user()
    return redirect(url_for('login'))

#用户管理路由
def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not current_user.is_authenticated or current_user.role != 'admin':
            # 如果用户未登录或不是管理员，重定向到首页
            return redirect(url_for('index'))
        return f(*args, **kwargs)
    return decorated_function

#直接注册路由
@app.route('/register', methods=['GET', 'POST'])
def register():
    if not app.config['REGISTER_ENABLED']:
        flash('注册功能已关闭！', 'error')
        return redirect(url_for('login'))
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        # 检查用户名是否已存在
        existing_user = User.query.filter_by(username=username).first()
        if existing_user:
            flash('该用户名已被注册！', 'error')
            return redirect(url_for('register'))
        
        # 如果用户名为 'admin'，则将角色设置为 'admin'
        role = 'admin' if username.lower() == 'admin' else 'user'
        
        # 创建新用户并保存到数据库
        new_user = User(username=username, password=generate_password_hash(password), role=role)
        db.session.add(new_user)
        db.session.commit()
        
        flash('注册成功！请登录。', 'success')
        return redirect(url_for('login'))
    return render_template('register.html')
    

@app.route('/user管理', methods=['GET', 'POST'])
@login_required
@admin_required
def user_management():
    if not current_user.is_authenticated or current_user.role != 'admin':
        flash('您没有权限访问该页面！', 'error')
        return redirect(url_for('index'))

    if request.method == 'POST':
        action = request.form.get('action')
        if action == 'register':
            return handle_user_registration()
        elif action == 'delete':
            return handle_user_deletion()
        elif action == 'toggle_register':
            app.config['REGISTER_ENABLED'] = not app.config['REGISTER_ENABLED']
            if app.config['REGISTER_ENABLED']:
                flash('注册功能已开启！', 'success')
            else:
                flash('注册功能已关闭！', 'success')

    # 查询所有用户信息
    users = User.query.all()
    return render_template('user_management.html', users=users, register_enabled=app.config['REGISTER_ENABLED'])

#管理员注册用户
def handle_user_registration():
    username = request.form['username']
    password = request.form['password']
    role = request.form.get('role', 'user')  # 默认角色为 'user'
    existing_user = User.query.filter_by(username=username).first()
    if existing_user:
        flash('该用户名已被注册！', 'error')
        return redirect(url_for('user_management'))
    
    new_user = User(username=username, password=generate_password_hash(password), role=role)
    db.session.add(new_user)
    db.session.commit()
    
    flash('用户注册成功！', 'success')
    return redirect(url_for('user_management'))
#管理员删除用户
def handle_user_deletion():
    user_id = request.form['user_id']
    user = db.session.get(User, user_id) #user = User.query.get(user_id)
    if user:
        db.session.delete(user)
        db.session.commit()
        flash('用户删除成功！', 'success')
    else:
        flash('用户不存在！', 'error')
    return redirect(url_for('user_management'))


# In[ ]:


#@app.before_first_request
#def create_upload_folder():
#    if not os.path.exists(app.config['UPLOAD_FOLDER']):
#        os.makedirs(app.config['UPLOAD_FOLDER'])

# 路由：主页
@app.route('/')
@login_required
def index():
    return render_template('index.html')

#允许上传的文件类型
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# 路由：添加产品
@app.route('/add_product', methods=['GET', 'POST']) #
@login_required
@admin_required
def add_product():
    if request.method == 'POST':
        name = request.form.get('name', '').strip()
        price = request.form.get('price', type=float)
        unit = request.form.get('unit', '').strip()  # 获取单位信息
        image = request.files.get('image')  # 获取上传的图片文件
        if not name or not price or not unit or price <= 0:
            flash("产品名称、价格和单位不能为空，价格必须大于0！", "error")
            return render_template('add_product.html', name=name, price=price,unit=unit)
        # 检查图片文件是否符合要求
        if image:
            if allowed_file(image.filename):  # 使用 allowed_file 函数检查文件类型
                filename = secure_filename(image.filename)
                image_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
                image.save(image_path)
                image_url = url_for('static', filename=os.path.join('uploads', filename).replace(os.path.sep, '/'))
                print(f"Generated image URL: {image_url}")  # 打印生成的 URL
            else:
                flash("无效的文件类型，请上传允许的图片格式！", "error")
                return render_template('add_product.html', name=name, price=price, unit=unit)
        else:
            image_url = None
        new_product = Product(name=name, price=price,unit=unit,image_url=image_url)
        db.session.add(new_product)
        db.session.commit()
        flash("产品添加成功！", "success")
        return redirect(url_for('list_products'))
    return render_template('add_product.html')

    
#创建变更图片的路由
@app.route('/change_product_image/<int:product_id>', methods=['GET', 'POST'])
@login_required
@admin_required
def change_product_image(product_id):
    product = db.session.get(Product, product_id)
    if not product:
        flash("产品不存在！", "error")
        return redirect(url_for('list_products'))

    if request.method == 'POST':
        file = request.files['image']
        if file and allowed_file(file.filename):
            filename = secure_filename(file.filename)
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            file.save(file_path)
            product.image_url = file_path
            db.session.commit()
            flash("产品图片已更新！", "success")
            return redirect(url_for('list_products'))
        else:
            flash("无效的文件类型！", "error")

    return render_template('change_product_image.html', product=product)



    
# 路由：删除产品
@app.route('/delete_product/<int:product_id>', methods=['GET', 'POST'])
@login_required
@admin_required
def delete_product(product_id):
    product = db.session.get(Product, product_id)
    if not product:
        flash("产品不存在！", "error")
        return redirect(url_for('list_products'))

    if product.quantity > 0:
        flash("无法删除有库存的产品！", "error")
        return redirect(url_for('list_products'))
        
    if request.method == 'POST':
        # 确认删除操作
        Purchase.query.filter_by(product_id=product_id).delete()
        Sale.query.filter_by(product_id=product_id).delete()
        db.session.delete(product)
        db.session.commit()
        flash("产品已成功删除！", "success")
        return redirect(url_for('list_products'))
    else:
        # 显示警告提示
        flash("警告：如果删除产品，将同时删除销售记录和采购记录，请确认是否删除！", "warning")
        return render_template('confirm_delete_product.html', product=product)

# 路由：列出所有产品
@app.route('/products', methods=['GET', 'POST'])
@login_required
@admin_required
def list_products():
    if request.method == 'POST':
        product_id = request.form.get('product_id', type=int)
        new_price = request.form.get('new_price', type=float)

        if not product_id or new_price is None:
            flash("无效的产品ID或价格！", "error")
            return redirect(url_for('list_products'))

        product = db.session.get(Product, product_id)
        if not product:
            flash("产品不存在！", "error")
            return redirect(url_for('list_products'))

        product.price = new_price
        db.session.commit()
        flash("产品价格已更新！", "success")

    # 从查询参数中获取产品名称
    name = request.args.get('name', default='', type=str)
    sort = request.args.get('sort', default='id', type=str)  # 获取排序字段，默认为'id'
    order = request.args.get('order', default='desc', type=str)  # 获取排序顺序，默认为'desc'
    page = request.args.get('page', default=1, type=int)
    per_page = 20  # 每页显示的产品数量

    # 构建查询
    query = Product.query
    if name:
        query = query.filter(Product.name.ilike(f'%{name}%'))

    # 添加排序
    if sort == 'name':
        query = query.order_by(desc(Product.name) if order == 'desc' else asc(Product.name))
    elif sort == 'price':
        query = query.order_by(desc(Product.price) if order == 'desc' else asc(Product.price))
    elif sort == 'quantity':
        query = query.order_by(desc(Product.quantity) if order == 'desc' else asc(Product.quantity))
    elif sort == 'total_value':
        query = query.order_by(desc(Product.price * Product.quantity) if order == 'desc' else asc(Product.price * Product.quantity))
    else:
        query = query.order_by(desc(Product.id) if order == 'desc' else asc(Product.id))

# 计算每个产品的平均进货价格
    product_avg_purchase_prices = {}
    total_quantity = 0
    total_value = 0.0
    total_net_value = 0.0

    # 遍历所有产品以计算总数量、总货值和总净值
    for product in query.all():
        purchases = Purchase.query.filter_by(product_id=product.id).all()
        total_purchase_price = sum(purchase.purchase_price * purchase.quantity*purchase.discount for purchase in purchases)
        total_purchase_quantity = sum(purchase.quantity for purchase in purchases)
        
        if total_purchase_quantity > 0:
            avg_purchase_price = total_purchase_price / total_purchase_quantity
        else:
            avg_purchase_price = 0.0  # 避免除以零的情况
        
        product_avg_purchase_prices[product.id] = avg_purchase_price

        total_quantity += product.quantity
        total_value += product.quantity * product.price
        total_net_value += avg_purchase_price * product.quantity

    # 应用分页逻辑
    products = query.paginate(page=page, per_page=per_page, error_out=False)

    return render_template(
        'products.html',
        products=products,
        product_avg_purchase_prices=product_avg_purchase_prices,
        total_quantity=total_quantity,
        total_value=total_value,
        total_net_value=total_net_value,
        name=name,
        sort=sort,
        order=order
    )



# 路由：店员销售产品
@app.route('/sales_products', methods=['GET'])
@login_required
def sales_products():
    # 从查询参数中获取产品名称
    name = request.args.get('name', default='', type=str)
    sort = request.args.get('sort', default='id', type=str)  # 获取排序字段，默认为'id'
    order = request.args.get('order', default='desc', type=str)  # 获取排序顺序，默认为'asc'
    page = request.args.get('page', 1, type=int)  # 获取当前页码，默认为1
    per_page = 30  # 每页显示的记录数
    
     # 构建查询
    query = Product.query
    if name:
        query = query.filter(Product.name.ilike(f'%{name}%'))

    # 添加排序
    if sort == 'name':
        query = query.order_by(desc(Product.name) if order == 'desc' else asc(Product.name))
    elif sort == 'price':
        query = query.order_by(desc(Product.price) if order == 'desc' else asc(Product.price))
    elif sort == 'quantity':
        query = query.order_by(desc(Product.quantity) if order == 'desc' else asc(Product.quantity))
    else:
        query = query.order_by(desc(Product.id) if order == 'desc' else asc(Product.id))

    products = query.paginate(page=page, per_page=per_page, error_out=False)

    return render_template('sales_products.html', products=products, name=name, sort=sort, order=order)


# 路由：店长销售产品
@app.route('/sell_product', methods=['GET', 'POST'])
@login_required
def sell_product():
    product_id = request.args.get('product_id', type=int)  # 获取产品ID
    today_date = datetime.today().strftime('%Y-%m-%d')
    customers = Customer.query.all()  # 获取所有客户
    
    if request.method == 'POST':
        product_id = request.form.get('product_id', type=int)
        quantity = request.form.get('quantity', type=int)
        sale_date = request.form.get('sale_date', type=lambda x: datetime.strptime(x, '%Y-%m-%d'))
        input_discount = request.form.get('discount', type=float, default=None)  # 获取折扣
        input_sale_price = request.form.get('sale_price', type=float, default=None)  # 获取销售单价
        salesperson = request.form.get('salesperson', type=str, default='')  # 获取销售人
        notes = request.form.get('notes', type=str, default='')  # 获取备注
        customer_id = request.form.get('customer_id', type=int, default=None)  # 获取客户ID
        payment_method = request.form.get('payment_method', type=str, default='cash')  # 获取支付方式
        # 查询产品
        product = db.session.get(Product, product_id)
        if not product:
            flash("产品不存在！", "error")
            return redirect(url_for('sell_product', product_id=product_id))

        if product.quantity < quantity:
            flash("库存不足，无法完成销售！", "error")
            return redirect(url_for('sell_product', product_id=product_id))

        # 计算折扣和销售单价
        if input_sale_price is not None and input_sale_price > 0:
            # 如果输入了销售单价，则计算折扣
            discount = input_sale_price / product.price
            sale_price = input_sale_price
        elif input_sale_price==0  or input_discount == 0:
             discount = 0
             sale_price = 0
        elif input_discount is not None and input_discount > 0:
            # 如果输入了折扣，则计算销售价格
            discount = input_discount
            sale_price = product.price * discount
        else:
            # 如果没有输入折扣或销售价格，默认折扣为1.0（无折扣）
            discount = 1.0
            sale_price = product.price

        # 更新库存
        product.quantity -= quantity
        db.session.add(product)

        # 记录库存现金收入
        current_balance = Cash.query.order_by(Cash.id.desc()).first().balance if Cash.query.count() > 0 else 0
        new_balance = current_balance + (sale_price * quantity)
        
        new_cash_income = Cash(
            amount = sale_price * quantity,
            transaction_type='收入',
            category='销售',
            description=f'销售产品 {product.name}，数量 {quantity}，销售人 {salesperson}，备注 {notes}',
            transaction_date=sale_date,
            balance=new_balance
        )
        db.session.add(new_cash_income)
        db.session.flush()  # 确保 new_cash_income 的 ID 被生成



        # 如果选择了客户并使用预存金额支付
        if customer_id and payment_method == 'prepaid':
            customer = db.session.get(Customer, customer_id)
            if not customer:
                flash("客户不存在！", "error")
                return redirect(url_for('sell_product', product_id=product_id))

            total_amount = sale_price * quantity

            # 检查客户是否允许透支
            if customer.allow_overdraft:
                # 允许透支，直接扣除金额（即使预存金额不足）
                customer.prepaid_amount -= total_amount
                db.session.add(customer)
                db.session.commit()  # 立即提交事务
            else:
                # 不允许透支，检查预存金额是否足够
                if customer.prepaid_amount < total_amount:
                    flash("客户预存金额不足，无法完成交易！", "error")
                    return redirect(url_for('sell_product', product_id=product_id))
                # 扣除金额
                customer.prepaid_amount -= total_amount
                db.session.add(customer)
                db.session.commit()  # 立即提交事务

        

        # 添加销售记录，记录销售时的标签价格和折扣
        new_sale = Sale(
            product_id=product_id,
            cash_id=new_cash_income.id,  # 设置 cash_id
            quantity=quantity,
            sale_date=sale_date,
            sale_price=product.price,
            discount=discount,
            salesperson=salesperson,  # 设置销售人
            notes=notes,  # 设置备注
            customer_id=customer_id,  # 设置客户ID
            payment_method=payment_method  # 设置支付方式
        )
        db.session.add(new_sale)

        # 提交事务
        db.session.commit()
        flash("销售记录已添加，库存已更新！", "success")
        return redirect(url_for('sales_products'))

    # 如果是 GET 请求，查询产品并传递给模板
    product = db.session.get(Product, product_id)
    if product:
        return render_template('sell_product.html', product=product, product_id=product_id, today_date=today_date, customers=customers)
    else:
        flash("产品不存在，请检查产品ID！", "error")
        return render_template('sell_product.html', product_id=product_id, today_date=today_date, customers=customers)


        
# 路由：列出所有销售记录
@app.route('/sales', methods=['GET'])
@login_required
def list_sales():
    search_query = request.args.get('search', '').strip()
    sort = request.args.get('sort', default='id', type=str)  # 获取排序字段，默认为'id'
    order = request.args.get('order', default='desc', type=str)  # 获取排序顺序，默认为'desc'
    page = request.args.get('page', 1, type=int)  # 获取当前页码，默认为1
    per_page = 20  # 每页显示的记录数

    # 初始化 query
    query = Sale.query.join(Product)

    # 构建查询，实现多字段搜索
    if search_query:
        query = query.filter(
            or_(
                Product.name.ilike(f'%{search_query}%'),
                Sale.sale_date.ilike(f'%{search_query}%'),
                Sale.salesperson.ilike(f'%{search_query}%'),
                Sale.notes.ilike(f'%{search_query}%')  
            )
        )

    # 添加排序
    if sort == 'product_name':
        query = query.order_by(desc(Product.name) if order == 'desc' else asc(Product.name))
    elif sort == 'quantity':
        query = query.order_by(desc(Sale.quantity) if order == 'desc' else asc(Sale.quantity))
    elif sort == 'sale_price':
        query = query.order_by(desc(Sale.sale_price) if order == 'desc' else asc(Sale.sale_price))
    elif sort == 'discount':
        query = query.order_by(desc(Sale.discount) if order == 'desc' else asc(Sale.discount))
    elif sort == 'discounted_price':
        query = query.order_by(desc(Sale.sale_price * Sale.discount) if order == 'desc' else asc(Sale.sale_price * Sale.discount))
    elif sort == 'total_sales':
        query = query.order_by(desc(Sale.sale_price * Sale.discount * Sale.quantity) if order == 'desc' else asc(Sale.sale_price * Sale.discount * Sale.quantity))
    elif sort == 'sale_date':
        query = query.order_by(desc(Sale.sale_date) if order == 'desc' else asc(Sale.sale_date))
    elif sort == 'salesperson':
        query = query.order_by(desc(Sale.salesperson) if order == 'desc' else asc(Sale.salesperson))
    else:
        query = query.order_by(desc(Sale.id) if order == 'desc' else asc(Sale.id))

    sales = query.paginate(page=page, per_page=per_page, error_out=False)

    return render_template('sales.html', sales=sales, search_query=search_query, sort=sort, order=order)


@app.route('/delete_sale/<int:sale_id>', methods=['POST'])
@login_required
@admin_required
def delete_sale(sale_id):
    sale = Sale.query.get_or_404(sale_id)
    product = Product.query.get_or_404(sale.product_id)

    # 如果是预存金额支付，返还金额到客户账户
    if sale.payment_method == 'prepaid' and sale.customer_id:
        customer = Customer.query.get_or_404(sale.customer_id)
        total_amount = sale.sale_price * sale.quantity * sale.discount
        customer.prepaid_amount += total_amount  # 返还金额
        db.session.add(customer)

    # 更新产品库存
    product.quantity += sale.quantity
    db.session.add(product)

    # 删除与销售记录关联的库存现金记录
    cash = Cash.query.get_or_404(sale.cash_id)
    db.session.delete(cash)

    # 删除销售记录
    db.session.delete(sale)
    db.session.commit()

    flash("销售记录已删除，产品库存和客户预存金额已更新！", "success")
    return redirect(url_for('list_sales'))
    

# 路由：采购产品
@app.route('/purchase_product', methods=['GET', 'POST'])
@login_required
@admin_required
def purchase_product():
    product_id = request.args.get('product_id', type=int)  # 获取产品ID
    today_date = datetime.today().strftime('%Y-%m-%d')
    suppliers = Supplier.query.all()  # 获取所有供应商

    if request.method == 'POST':
        product_id = request.form.get('product_id', type=int)
        quantity = request.form.get('quantity', type=int)
        purchase_date = request.form.get('purchase_date', type=lambda x: datetime.strptime(x, '%Y-%m-%d'))
        discount = request.form.get('discount', type=float, default=1.0)  # 折扣，默认为1.0（无折扣）
        input_purchase_price = request.form.get('purchase_price', type=float, default=None)  # 获取采购单价
        purchase_person = request.form.get('purchase_person', type=str, default='')  # 获取采购人
        notes = request.form.get('notes', type=str, default='')  # 获取备注
        supplier_id = request.form.get('supplier_id', type=int, default=None)  # 获取供应商ID
        payment_method = request.form.get('payment_method', type=str, default='cash')  # 获取支付方式

        # 查询产品
        product = db.session.get(Product, product_id)
        if not product:
            flash("产品不存在！", "error")
            return render_template('purchase_product.html', product_id=product_id, today_date=today_date, suppliers=suppliers)

        # 计算折扣和采购单价
        if input_purchase_price is not None and input_purchase_price > 0:
            # 如果输入了采购单价，则计算折扣
            discount = input_purchase_price / product.price
            purchase_price = input_purchase_price
        elif input_purchase_price == 0 or discount == 0:
            discount = 0
            purchase_price = 0
        elif discount is not None and discount > 0:
            # 如果输入了折扣，则计算采购单价
            purchase_price = product.price * discount
        else:
            # 如果没有输入折扣或采购价格，默认折扣为1.0（无折扣），价格为标签价格
            discount = 1.0
            purchase_price = product.price

        # 更新库存
        product.quantity += quantity
        db.session.add(product)

                # 如果选择了供应商，使用供应商账户金额支付
        if supplier_id:
            supplier = db.session.get(Supplier, supplier_id)
            if not supplier:
                flash("供应商不存在！", "error")
                return render_template('purchase_product.html', product_id=product_id, today_date=today_date, suppliers=suppliers)

            total_amount = purchase_price * quantity

            # 检查供应商账户金额是否足够
            if supplier.prepaid_amount < total_amount:
                flash("供应商账户金额不足，产生赊购！")

            # 扣除供应商账户金额
            supplier.prepaid_amount -= total_amount
            db.session.add(supplier)

            # 添加采购记录，设置 supplier_id
            new_purchase = Purchase(
                product_id=product_id,
                supplier_id=supplier_id,  # 设置供应商ID
                cash_id=None,  # 确保 cash_id 为 NULL
                quantity=quantity,
                purchase_date=purchase_date,
                purchase_price=product.price,
                discount=discount,
                purchase_person=purchase_person,
                notes=notes
            )
            db.session.add(new_purchase)

        # 如果没有选择供应商（即选择散户），使用现金支付
        else:
            # 记录库存现金支出
            current_balance = Cash.query.order_by(Cash.id.desc()).first().balance if Cash.query.count() > 0 else 0
            new_balance = current_balance - (purchase_price * quantity)

            new_cash_expense = Cash(
                amount=purchase_price * quantity,
                transaction_type='支出',
                category='采购',
                description=f'采购产品 {product.name}，数量 {quantity}，采购人 {purchase_person}，备注 {notes}',
                transaction_date=purchase_date,
                balance=new_balance
            )
            db.session.add(new_cash_expense)
            db.session.flush()  # 确保 new_cash_expense 的 ID 被生成

            # 添加采购记录，设置 cash_id
            new_purchase = Purchase(
                product_id=product_id,
                supplier_id=None,  # 确保 supplier_id 为 NULL
                cash_id=new_cash_expense.id,  # 设置 cash_id
                quantity=quantity,
                purchase_date=purchase_date,
                purchase_price=product.price,
                discount=discount,
                purchase_person=purchase_person,
                notes=notes
            )
            db.session.add(new_purchase)

        # 提交事务
        db.session.commit()
        purchase_all_price = purchase_price * quantity
        flash(f"采购记录已添加，库存已更新！实际采购金额：{purchase_all_price:.2f}", "success")
        return redirect(url_for('list_purchases'))

    # 如果是 GET 请求，查询产品并传递给模板
    product = db.session.get(Product, product_id)
    if product:
        return render_template('purchase_product.html', product=product, product_id=product_id, today_date=today_date, suppliers=suppliers)
    else:
        flash("产品不存在，请检查产品ID！", "error")
        return render_template('purchase_product.html', product_id=product_id, today_date=today_date, suppliers=suppliers)



#创建库存现金记录路由
@app.route('/cash_records')
@login_required
@admin_required
def cash_records():
    search_query = request.args.get('search', '').strip()
    page = request.args.get('page', default=1, type=int)
    per_page = 20  # 每页显示的记录数

    # 构建查询
    query = Cash.query
    if search_query:
        query = query.filter(
            or_(
                Cash.amount.ilike(f'%{search_query}%'),
                Cash.transaction_type.ilike(f'%{search_query}%'),
                Cash.category.ilike(f'%{search_query}%'),
                Cash.description.ilike(f'%{search_query}%'),
                Cash.transaction_date.ilike(f'%{search_query}%')
            )
        )

    # 获取所有库存现金记录并分页
    cash_records = query.order_by(Cash.id.desc()).paginate(page=page, per_page=per_page, error_out=False)

    # 获取最后一条库存现金记录的交易后余额
    last_cash_record = query.order_by(Cash.id.desc()).first()
    total_balance = last_cash_record.balance if last_cash_record else 0
    # 获取预存现金总余额
    total_predeposit_balance = get_total_predeposit_balance()  
    #获取两个数据的合计
    total_cash_balance = total_balance + total_predeposit_balance
    
    return render_template(
        'cash_records.html',
        cash_records=cash_records,
        total_balance=total_balance,
        total_predeposit_balance = total_predeposit_balance,
        total_cash_balance = total_cash_balance,
        search_query=search_query
    )
    
#添加其他收入路由
@app.route('/add_income', methods=['GET', 'POST'])
@login_required
@admin_required
def add_income():
    today_date = datetime.today().strftime('%Y-%m-%d')
    if request.method == 'POST':
        amount = request.form.get('amount', type=float)
        description = request.form.get('description', '').strip()
        transaction_date = request.form.get('transaction_date', type=lambda x: datetime.strptime(x, '%Y-%m-%d'))

        if not amount or amount <= 0:
            flash("金额必须大于0！", "error")
            return render_template('add_income.html')

        # 记录其他收入
        current_balance = Cash.query.order_by(Cash.id.desc()).first().balance if Cash.query.count() > 0 else 0
        new_balance = current_balance + amount
        
        new_cash_income = Cash(
            amount=amount,
            transaction_type='收入',
            category='其他收入',
            description=description,
            transaction_date=transaction_date,
            balance=new_balance
        )
        db.session.add(new_cash_income)
        
        db.session.commit()
        flash(f"收入记录已添加！金额：{amount:.2f}", "success")
        return redirect(url_for('cash_records'))
    return render_template('add_income.html',today_date=today_date)

#添加其他支出路由
@app.route('/add_expense', methods=['GET', 'POST'])
@login_required
@admin_required
def add_expense():
    today_date = datetime.today().strftime('%Y-%m-%d')
    if request.method == 'POST':
        amount = request.form.get('amount', type=float)
        description = request.form.get('description', '').strip()
        transaction_date = request.form.get('transaction_date', type=lambda x: datetime.strptime(x, '%Y-%m-%d'))

        if not amount or amount <= 0:
            flash("金额必须大于0！", "error")
            return render_template('add_expense.html')

        # 记录其他支出
        current_balance = Cash.query.order_by(Cash.id.desc()).first().balance if Cash.query.count() > 0 else 0
        new_balance = current_balance - amount
        
        new_cash_expense = Cash(
            amount=amount,
            transaction_type='支出',
            category='其他支出',
            description=description,
            transaction_date=transaction_date,
            balance=new_balance
        )
        db.session.add(new_cash_expense)
        
        db.session.commit()
        flash(f"支出记录已添加！金额：{amount:.2f}", "success")
        return redirect(url_for('cash_records'))

    return render_template('add_expense.html',today_date=today_date)
        

# 路由：列出所有采购记录
@app.route('/purchases')
@login_required
@admin_required
def list_purchases():
    search_query = request.args.get('search', '').strip()
    sort = request.args.get('sort', default='id', type=str)  # 获取排序字段，默认为'id'
    order = request.args.get('order', default='desc', type=str)  # 获取排序顺序，默认为'desc'
    page = request.args.get('page', 1, type=int)  # 获取当前页码，默认为1
    per_page = 20  # 每页显示的记录数

    # 构建查询
    query = Purchase.query.join(Product)
    
    # 多字段搜索
    if search_query:
        query = query.filter(
            or_(
                Product.name.ilike(f'%{search_query}%'),  # 产品名称
                Purchase.purchase_date.ilike(f'%{search_query}%'),  # 采购日期
                Purchase.purchase_person.ilike(f'%{search_query}%'),  # 采购人
                Purchase.notes.ilike(f'%{search_query}%')  # 备注字段
            )
        )

    # 添加排序
    if sort == 'product_name':
        query = query.order_by(desc(Product.name) if order == 'desc' else asc(Product.name))
    elif sort == 'unit':
        query = query.order_by(desc(Product.unit) if order == 'desc' else asc(Product.unit))
    elif sort == 'purchase_price':
        query = query.order_by(desc(Purchase.purchase_price) if order == 'desc' else asc(Purchase.purchase_price))
    elif sort == 'discount':
        query = query.order_by(desc(Purchase.discount) if order == 'desc' else asc(Purchase.discount))
    elif sort == 'discounted_price':
        query = query.order_by(desc(Purchase.purchase_price * Purchase.discount) if order == 'desc' else asc(Purchase.purchase_price * Purchase.discount))
    elif sort == 'quantity':
        query = query.order_by(desc(Purchase.quantity) if order == 'desc' else asc(Purchase.quantity))
    elif sort == 'total_purchase':
        query = query.order_by(desc(Purchase.purchase_price * Purchase.discount * Purchase.quantity) if order == 'desc' else asc(Purchase.purchase_price * Purchase.discount * Purchase.quantity))
    elif sort == 'purchase_date':
        query = query.order_by(desc(Purchase.purchase_date) if order == 'desc' else asc(Purchase.purchase_date))
    elif sort == 'purchase_person':
        query = query.order_by(desc(Purchase.purchase_person) if order == 'desc' else asc(Purchase.purchase_person))
    else:
        query = query.order_by(desc(Purchase.id) if order == 'desc' else asc(Purchase.id))

    purchases = query.paginate(page=page, per_page=per_page, error_out=False)

    return render_template('purchases.html', purchases=purchases, search_query=search_query, sort=sort, order=order)


#删除采购记录的路由
@app.route('/delete_purchase/<int:purchase_id>', methods=['POST'])
@login_required
@admin_required
def delete_purchase(purchase_id):
    purchase = Purchase.query.get_or_404(purchase_id)
    product = Product.query.get_or_404(purchase.product_id)

    # 更新产品库存
    product.quantity -= purchase.quantity
    db.session.add(product)

    # 如果存在供应商ID，则假设使用了供应商账户金额
    if purchase.supplier_id:
        supplier = Supplier.query.get_or_404(purchase.supplier_id)
        # 将采购金额加回到供应商的预存金额中
        supplier.prepaid_amount += purchase.purchase_price * purchase.quantity * purchase.discount
        db.session.add(supplier)

    # 如果存在现金记录（cash_id 不为空），则删除关联的库存现金记录
    if purchase.cash_id:
        cash = Cash.query.get_or_404(purchase.cash_id)
        db.session.delete(cash)

    # 删除采购记录
    db.session.delete(purchase)
    db.session.commit()

    flash("采购记录已删除，产品库存和现金记录已更新！供应商账户金额已恢复（如果适用）。", "success")
    return redirect(url_for('list_purchases'))



# 路由：查看库存
@app.route('/inventory')
@login_required
def inventory():

    search_query = request.args.get('search', '').strip()
    sort = request.args.get('sort', default='id', type=str)  # 获取排序字段，默认为'id'
    order = request.args.get('order', default='desc', type=str)  # 获取排序顺序，默认为'desc'
    page = request.args.get('page', 1, type=int)  # 获取当前页码，默认为1
    per_page = 20  # 每页显示的记录数

    # 构建查询
    query = Product.query
    if search_query:
        query = query.filter(Product.name.ilike(f'%{search_query}%'))

    # 添加排序
    if sort == 'name':
        query = query.order_by(desc(Product.name) if order == 'desc' else asc(Product.name))
    elif sort == 'price':
        query = query.order_by(desc(Product.price) if order == 'desc' else asc(Product.price))
    elif sort == 'quantity':
        query = query.order_by(desc(Product.quantity) if order == 'desc' else asc(Product.quantity))
    elif sort == 'value':  # 新增货值排序
        query = query.order_by(desc(Product.price * Product.quantity) if order == 'desc' else asc(Product.price * Product.quantity))
    else:
        query = query.order_by(desc(Product.id) if order == 'desc' else asc(Product.id))

    products = query.paginate(page=page, per_page=per_page, error_out=False)

    return render_template('inventory.html', products=products, search_query=search_query, sort=sort, order=order)
    

# 管理客户的路由
# 路由：添加客户
@app.route('/add_customer', methods=['GET', 'POST'])
@login_required
@admin_required
def add_customer():
    if request.method == 'POST':
        name = request.form.get('name', '').strip()
        prepaid_amount = request.form.get('prepaid_amount', type=float, default=0.0)
        account_manager = request.form.get('account_manager', '').strip()

        if not name:
            flash("客户名称不能为空！", "error")
            return render_template('add_customer.html')

        new_customer = Customer(name=name, prepaid_amount=prepaid_amount, account_manager=account_manager)
        db.session.add(new_customer)
        db.session.commit()

        # 如果有预存金额，记录首次存入
        if prepaid_amount > 0:
            new_deposit = Deposit(
                customer_id=new_customer.id,
                amount=prepaid_amount,
                description="首次存入"
            )
            db.session.add(new_deposit)
            db.session.commit()

        flash("客户添加成功！", "success")
        return redirect(url_for('list_customers'))

    return render_template('add_customer.html')

# 路由：列出所有客户
@app.route('/customers')
@login_required
@admin_required
def list_customers():
    #customers = Customer.query.all()
    search_query = request.args.get('search', '').strip()
    sort = request.args.get('sort', default='id', type=str)  # 获取排序字段，默认为'id'
    order = request.args.get('order', default='desc', type=str)  # 获取排序顺序，默认为'desc'
    page = request.args.get('page', 1, type=int)  # 获取当前页码，默认为1
    per_page = 20  # 每页显示的记录数
    # 构建查询
    query = Customer.query
    #if search_query:
    #    query = query.filter(Customer.name.ilike(f'%{search_query}%'))
    # 多字段搜索
    if search_query:
        query = query.filter(
            or_(
                Customer.name.ilike(f'%{search_query}%'),  # 客户名称
                Customer.prepaid_amount.ilike(f'%{search_query}%'),  # 客户账户金额
                Customer.account_manager.ilike(f'%{search_query}%'),  # 开户人
                Customer.created_at.ilike(f'%{search_query}%')  # 开户时间
            )
        )

    # 排序
    if sort == 'name':
        query = query.order_by(Customer.name.asc() if order == 'asc' else Customer.name.desc())
    elif sort == 'prepaid_amount':
        query = query.order_by(Customer.prepaid_amount.asc() if order == 'asc' else Customer.prepaid_amount.desc())
    elif sort == 'account_manager':
        query = query.order_by(Customer.account_manager.asc() if order == 'asc' else Customer.account_manager.desc())
    elif sort == 'created_at':
        query = query.order_by(Customer.created_at.asc() if order == 'asc' else Customer.created_at.desc())
    else:
        query = query.order_by(Customer.id.asc() if order == 'asc' else Customer.id.desc())

    # 分页
    customers = query.paginate(page=page, per_page=per_page)
    
    return render_template('customers.html',  customers=customers, 
                           search_query=search_query, 
                           sort=sort, 
                           order=order)

# 路由：删除客户
@app.route('/delete_customer/<int:customer_id>', methods=['POST'])
@login_required
@admin_required
def delete_customer(customer_id):
    customer = db.session.get(Customer, customer_id)
    if not customer:
        flash("客户不存在！", "error")
        return redirect(url_for('list_customers'))

    # 检查客户是否有未完成的销售记录
    if customer.sales:
        flash("该客户有未完成的销售记录，无法删除！", "error")
        return redirect(url_for('list_customers'))

    # 删除与该客户相关的所有存入记录
    Deposit.query.filter_by(customer_id=customer_id).delete()

    # 删除客户
    db.session.delete(customer)
    db.session.commit()

    flash("客户删除成功！", "success")
    return redirect(url_for('list_customers'))
    

#客户存入金额路由
@app.route('/add_customer_amount/<int:customer_id>', methods=['GET', 'POST'])
@login_required
@admin_required
def add_customer_amount(customer_id):
    customer = db.session.get(Customer, customer_id)
    if not customer:
        flash("客户不存在！", "error")
        return redirect(url_for('list_customers'))

    if request.method == 'POST':
        amount = request.form.get('amount', type=float)
        description = request.form.get('description', '').strip()

        # 检查金额是否有效
        if not amount:
            flash("金额不能为空！", "error")
            return redirect(url_for('add_customer_amount', customer_id=customer_id))

        # 检查是否允许透支
        if not customer.allow_overdraft and (customer.prepaid_amount + amount) < 0:
            flash("客户不允许透支，减少金额后余额不能为负数！", "error")
            return redirect(url_for('add_customer_amount', customer_id=customer_id))

        # 更新客户预存金额
        customer.prepaid_amount += amount
        db.session.add(customer)

        # 记录存入明细
        new_deposit = Deposit(
            customer_id=customer_id,
            amount=amount,
            description=description
        )
        db.session.add(new_deposit)

        db.session.commit()

        # 根据金额正负值生成提示信息
        if amount >= 0:
            flash(f"成功为客户 {customer.name} 增加存入金额 {amount:.2f} 元！", "success")
        else:
            flash(f"成功为客户 {customer.name} 减少存入金额 {-amount:.2f} 元！", "success")

        return redirect(url_for('list_customers'))

    return render_template('add_customer_amount.html', customer=customer)
    


#允许透支切换路由
@app.route('/toggle_overdraft/<int:customer_id>', methods=['POST'])
@login_required
@admin_required
def toggle_overdraft(customer_id):
    customer = db.session.get(Customer, customer_id)
    if not customer:
        return jsonify({'success': False, 'message': '客户不存在！'})

    data = request.get_json()
    allow_overdraft = data.get('allow_overdraft', False)

    # 更新允许透支状态
    customer.allow_overdraft = allow_overdraft
    db.session.commit()

    return jsonify({'success': True, 'message': '允许透支状态已更新！'})


# 路由：展示客户购买明细
@app.route('/customer_sales/<int:customer_id>')
@login_required
def customer_sales(customer_id):
    customer = Customer.query.get_or_404(customer_id)  # 获取客户信息，不存在则返回 404

    # 获取分页和排序参数
    search_query = request.args.get('search', '').strip()  # 搜索关键字
    sort = request.args.get('sort', default='sale_date', type=str)  # 排序字段，默认按销售日期
    order = request.args.get('order', default='desc', type=str)  # 排序顺序，默认降序
    page = request.args.get('page', 1, type=int)  # 当前页码，默认为第一页
    per_page = 10  # 每页显示的记录数

    # 构建查询
    query = Sale.query.filter_by(customer_id=customer_id)  # 仅查询该客户的购买记录

    # 搜索功能
    if search_query:
        # 将用户输入的搜索关键字映射到数据库字段值
        if search_query == "现金":
            search_query = "cash"
        elif search_query == "预存金额":
            search_query = "prepaid"
        else:
            search_query = search_query.lower()  # 保持对其他字段的大小写不敏感搜索
            
        query = query.filter(
            or_(
                Sale.product.has(Product.name.ilike(f'%{search_query}%')),  # 按产品名称搜索
                Sale.salesperson.ilike(f'%{search_query}%'),  # 按销售人搜索
                Sale.sale_date.ilike(f'%{search_query}%'),  # 按销售日期搜索
                Sale.payment_method.ilike(f'%{search_query}%')  # 按支付方式搜索
            )
        )

    # 分页
    sales = query.paginate(page=page, per_page=per_page)

    # 在 Python 中计算总金额并排序
    for sale in sales.items:
        sale.total_amount = sale.sale_price * sale.quantity * sale.discount

    if sort == 'total_amount':
        sales.items = sorted(sales.items, key=lambda x: x.total_amount, reverse=(order == 'desc'))
    elif sort == 'sale_date':
        sales.items = sorted(sales.items, key=lambda x: x.sale_date, reverse=(order == 'desc'))
    elif sort == 'salesperson':
        sales.items = sorted(sales.items, key=lambda x: x.salesperson, reverse=(order == 'desc'))
    else:
        sales.items = sorted(sales.items, key=lambda x: x.id, reverse=(order == 'desc'))

    return render_template('customer_sales.html', 
                           customer=customer, 
                           sales=sales, 
                           search_query=search_query, 
                           sort=sort, 
                           order=order)
    

#客户的存入金额明细
@app.route('/view_customer_deposits/<int:customer_id>')
@login_required
@admin_required
def view_customer_deposits(customer_id):
    customer = db.session.get(Customer, customer_id)
    if not customer:
        flash("客户不存在！", "error")
        return redirect(url_for('list_customers'))

    # 获取分页和排序参数
    search_query = request.args.get('search', '').strip()  # 搜索关键字
    sort = request.args.get('sort', default='deposit_date', type=str)  # 排序字段，默认按存入时间
    order = request.args.get('order', default='desc', type=str)  # 排序顺序，默认降序
    page = request.args.get('page', 1, type=int)  # 当前页码，默认为第一页
    per_page = 20  # 每页显示的记录数

    # 构建查询
    query = Deposit.query.filter_by(customer_id=customer_id)

    # 搜索功能
    if search_query:
        try:
            # 尝试将搜索关键字转换为浮点数，用于存入金额的精确搜索
            search_amount = float(search_query)
            query = query.filter(Deposit.amount == search_amount)
        except ValueError:
            # 如果搜索关键字不是数字，则按存入时间和描述进行模糊搜索
            query = query.filter(
                or_(
                    Deposit.deposit_date.ilike(f'%{search_query}%'),  # 按存入时间搜索
                    Deposit.description.ilike(f'%{search_query}%')  # 按描述搜索
                )
            )

    # 排序功能
    if sort == 'deposit_date':
        query = query.order_by(Deposit.deposit_date.asc() if order == 'asc' else Deposit.deposit_date.desc())
    elif sort == 'amount':
        query = query.order_by(Deposit.amount.asc() if order == 'asc' else Deposit.amount.desc())
    else:
        query = query.order_by(Deposit.id.asc() if order == 'asc' else Deposit.id.desc())

    # 分页
    deposits = query.paginate(page=page, per_page=per_page)

    return render_template('view_customer_deposits.html', 
                           customer=customer, 
                           deposits=deposits, 
                           search_query=search_query, 
                           sort=sort, 
                           order=order)

#计算所有客户的预存现金总余额的函数
def get_total_predeposit_balance():
    """计算所有客户的预存现金总余额"""
    total_predeposit_balance = db.session.query(db.func.sum(Customer.prepaid_amount)).scalar()
    return total_predeposit_balance or 0.0  # 如果没有客户，返回 0.0


#添加供应商
@app.route('/add_supplier', methods=['GET', 'POST'])
@login_required
@admin_required
def add_supplier():
    if request.method == 'POST':
        name = request.form.get('name', '').strip()
        contact_person = request.form.get('contact_person', '').strip()
        contact_phone = request.form.get('contact_phone', '').strip()
        address = request.form.get('address', '').strip()

        if not name:
            flash("供应商名称不能为空！", "error")
            return render_template('add_supplier.html')

        new_supplier = Supplier(
            name=name,
            prepaid_amount=0.0,  # 默认值为 0
            contact_person=contact_person,
            contact_phone=contact_phone,
            address=address
        )
        db.session.add(new_supplier)
        db.session.commit()

        flash("供应商添加成功！", "success")
        return redirect(url_for('list_suppliers'))

    return render_template('add_supplier.html')




#列出所有供应商
@app.route('/suppliers')
@login_required
@admin_required
def list_suppliers():
    search_query = request.args.get('search', '').strip()
    sort = request.args.get('sort', default='id', type=str)  # 排序字段，默认为'id'
    order = request.args.get('order', default='desc', type=str)  # 排序顺序，默认为'desc'
    page = request.args.get('page', 1, type=int)  # 当前页码，默认为1
    per_page = 20  # 每页显示的记录数

    # 构建查询
    query = Supplier.query

    # 多字段搜索
    if search_query:
        query = query.filter(
            or_(
                Supplier.name.ilike(f'%{search_query}%'),  # 供应商名称
                Supplier.contact_person.ilike(f'%{search_query}%'),  # 联系人
                Supplier.contact_phone.ilike(f'%{search_query}%'),  # 联系电话
                Supplier.address.ilike(f'%{search_query}%')  # 地址
            )
        )

    # 排序
    if sort == 'name':
        query = query.order_by(Supplier.name.asc() if order == 'asc' else Supplier.name.desc())
    elif sort == 'contact_person':
        query = query.order_by(Supplier.contact_person.asc() if order == 'asc' else Supplier.contact_person.desc())
    elif sort == 'contact_phone':
        query = query.order_by(Supplier.contact_phone.asc() if order == 'asc' else Supplier.contact_phone.desc())
    elif sort == 'address':
        query = query.order_by(Supplier.address.asc() if order == 'asc' else Supplier.address.desc())
    elif sort == 'created_at':
        query = query.order_by(Supplier.created_at.asc() if order == 'asc' else Supplier.created_at.desc())
    else:
        query = query.order_by(Supplier.id.asc() if order == 'asc' else Supplier.id.desc())

    # 分页
    suppliers = query.paginate(page=page, per_page=per_page)

    return render_template('suppliers.html', 
                           suppliers=suppliers, 
                           search_query=search_query, 
                           sort=sort, 
                           order=order)

#删除供应商
@app.route('/delete_supplier/<int:supplier_id>', methods=['POST'])
@login_required
@admin_required
def delete_supplier(supplier_id):
    supplier = db.session.get(Supplier, supplier_id)
    if not supplier:
        flash("供应商不存在！", "error")
        return redirect(url_for('list_suppliers'))

    # 检查供应商账户金额（预存金额）是否为0
    if supplier.prepaid_amount != 0:
        flash("该供应商的账户金额不为0，无法删除！", "error")
        return redirect(url_for('list_suppliers'))

    # 删除与该供应商相关的所有存入记录
    SupplierDeposit.query.filter_by(supplier_id=supplier_id).delete()

    # 删除供应商
    db.session.delete(supplier)
    db.session.commit()

    flash("供应商删除成功！", "success")
    return redirect(url_for('list_suppliers'))


#编辑供应商
@app.route('/edit_supplier/<int:supplier_id>', methods=['GET', 'POST'])
@login_required
@admin_required
def edit_supplier(supplier_id):
    supplier = db.session.get(Supplier, supplier_id)
    if not supplier:
        flash("供应商不存在！", "error")
        return redirect(url_for('list_suppliers'))

    if request.method == 'POST':
        supplier.name = request.form.get('name', '').strip()
        supplier.contact_person = request.form.get('contact_person', '').strip()
        supplier.contact_phone = request.form.get('contact_phone', '').strip()
        supplier.address = request.form.get('address', '').strip()

        if not supplier.name:
            flash("供应商名称不能为空！", "error")
            return render_template('edit_supplier.html', supplier=supplier)

        db.session.commit()
        flash("供应商信息更新成功！", "success")
        return redirect(url_for('list_suppliers'))

    return render_template('edit_supplier.html', supplier=supplier)

#列出供应商供货明细
@app.route('/supplier_supplies/<int:supplier_id>')
@login_required
@admin_required
def supplier_supplies(supplier_id):
    supplier = Supplier.query.get_or_404(supplier_id)  # 获取供应商信息，不存在则返回 404

    # 获取分页和排序参数
    search_query = request.args.get('search', '').strip()  # 搜索关键字
    sort = request.args.get('sort', default='id', type=str)  # 排序字段，默认按供货日期  default='purchase_date',
    order = request.args.get('order', default='desc', type=str)  # 排序顺序，默认降序
    page = request.args.get('page', 1, type=int)  # 当前页码，默认为第一页
    per_page = 10  # 每页显示的记录数

    # 构建查询
    query = Purchase.query.filter_by(supplier_id=supplier_id)  # 仅查询该供应商的供货记录

    # 搜索功能
    if search_query:
        query = query.filter(
            or_(
                Purchase.product.has(Product.name.ilike(f'%{search_query}%')),  # 按产品名称搜索
                Purchase.purchase_date.ilike(f'%{search_query}%'),  # 按供货日期搜索
                Purchase.notes.ilike(f'%{search_query}%')  # 按备注搜索
            )
        )

    # 分页
    supplies = query.paginate(page=page, per_page=per_page)

    # 在 Python 中计算总金额并排序
    for supply in supplies.items:
        supply.total_amount = supply.purchase_price * supply.quantity
    if sort == 'total_amount':
        supplies.items = sorted(supplies.items, key=lambda x: x.total_amount, reverse=(order == 'desc'))
    elif sort == 'purchase_date':
        supplies.items = sorted(supplies.items, key=lambda x: x.purchase_date, reverse=(order == 'desc'))
    else:
        supplies.items = sorted(supplies.items, key=lambda x: x.id, reverse=(order == 'desc'))

    return render_template('supplier_supplies.html', 
                           supplier=supplier, 
                           supplies=supplies, 
                           search_query=search_query, 
                           sort=sort, 
                           order=order)

#存入供应商金额路由
@app.route('/add_supplier_amount/<int:supplier_id>', methods=['GET', 'POST'])
@login_required
@admin_required
def add_supplier_amount(supplier_id):
    supplier = db.session.get(Supplier, supplier_id)
    if not supplier:
        flash("供应商不存在！", "error")
        return redirect(url_for('list_suppliers'))

    if request.method == 'POST':
        amount = request.form.get('amount', type=float)
        description = request.form.get('description', '').strip()

        # 检查金额是否有效
        if not amount:
            flash("金额不能为空！", "error")
            return redirect(url_for('add_supplier_amount', supplier_id=supplier_id))

        # 更新供应商预存金额
        supplier.prepaid_amount += amount
        db.session.add(supplier)

        # 记录存入明细
        new_deposit = SupplierDeposit(
            supplier_id=supplier_id,
            amount=amount,
            description=description
        )
        db.session.add(new_deposit)

        # 获取当前库存现金余额
        current_cash = db.session.query(Cash).order_by(Cash.id.desc()).first()
        if not current_cash:
            current_cash_balance = 0
        else:
            current_cash_balance = current_cash.balance

        # 根据金额的正负值更新库存现金余额
        if amount > 0:
            # 存入供应商账户，库存现金减少
            new_cash_balance = current_cash_balance - amount
            transaction_type = "支出"
            category = "付供应商款"
            flash_message = f"成功为供应商 {supplier.name} 增加存入金额 {amount:.2f} 元！"
        else:
            # 从供应商账户取出金额，库存现金增加
            new_cash_balance = current_cash_balance - amount  # 注意这里是减去负数，即增加余额
            transaction_type = "收入"
            category = "收供应商款"
            flash_message = f"成功为供应商 {supplier.name} 减少存入金额 {-amount:.2f} 元！"

        # 记录库存现金交易
        new_cash_transaction = Cash(
            amount=abs(amount),  # 交易金额总是正数
            transaction_type=transaction_type,
            category=category,
            description=f"存入供应商 {supplier.name} 账户金额: {amount:.2f} 元",
            transaction_date=datetime.utcnow(),
            balance=new_cash_balance
        )
        db.session.add(new_cash_transaction)

        db.session.commit()

        flash(flash_message, "success")
        return redirect(url_for('list_suppliers'))

    return render_template('add_supplier_amount.html', supplier=supplier)

#查看供应商存入金额明细路由
@app.route('/view_supplier_deposits/<int:supplier_id>')
@login_required
@admin_required
def view_supplier_deposits(supplier_id):
    supplier = db.session.get(Supplier, supplier_id)
    if not supplier:
        flash("供应商不存在！", "error")
        return redirect(url_for('list_suppliers'))

    # 获取分页和排序参数
    search_query = request.args.get('search', '').strip()  # 搜索关键字
    sort = request.args.get('sort', default='deposit_date', type=str)  # 排序字段，默认按存入时间
    order = request.args.get('order', default='desc', type=str)  # 排序顺序，默认降序
    page = request.args.get('page', 1, type=int)  # 当前页码，默认为第一页
    per_page = 20  # 每页显示的记录数

    # 构建查询
    query = SupplierDeposit.query.filter_by(supplier_id=supplier_id)

    # 搜索功能
    if search_query:
        try:
            # 尝试将搜索关键字转换为浮点数，用于存入金额的精确搜索
            search_amount = float(search_query)
            query = query.filter(SupplierDeposit.amount == search_amount)
        except ValueError:
            # 如果搜索关键字不是数字，则按存入时间和描述进行模糊搜索
            query = query.filter(
                or_(
                    SupplierDeposit.deposit_date.ilike(f'%{search_query}%'),  # 按存入时间搜索
                    SupplierDeposit.description.ilike(f'%{search_query}%')  # 按描述搜索
                )
            )

    # 排序功能
    if sort == 'deposit_date':
        query = query.order_by(SupplierDeposit.deposit_date.asc() if order == 'asc' else SupplierDeposit.deposit_date.desc())
    elif sort == 'amount':
        query = query.order_by(SupplierDeposit.amount.asc() if order == 'asc' else SupplierDeposit.amount.desc())
    else:
        query = query.order_by(SupplierDeposit.id.asc() if order == 'asc' else SupplierDeposit.id.desc())

    # 分页
    deposits = query.paginate(page=page, per_page=per_page)

    return render_template('view_supplier_deposits.html', 
                           supplier=supplier, 
                           deposits=deposits, 
                           search_query=search_query, 
                           sort=sort, 
                           order=order)



@app.route('/statistics')
@login_required
@admin_required
def statistics():
    # 计算库存总数量、总净值、总货值
    products = Product.query.all()
    total_quantity = sum(product.quantity for product in products)
    total_value = sum(product.quantity * product.price for product in products)
    # 计算总产品数量
    total_products = len(products)
    # 计算库存大于 0 的产品数量
    products_with_stock = len([product for product in products if product.quantity > 0])
    
    # 计算每个产品的平均进货价格
    product_avg_purchase_prices = {}
    total_net_value = 0.0
    for product in products:
        purchases = Purchase.query.filter_by(product_id=product.id).all()
        total_purchase_price = sum(purchase.purchase_price * purchase.quantity * purchase.discount for purchase in purchases)
        total_purchase_quantity = sum(purchase.quantity for purchase in purchases)
        
        if total_purchase_quantity > 0:
            avg_purchase_price = total_purchase_price / total_purchase_quantity
        else:
            avg_purchase_price = 0.0  # 避免除以零的情况
        
        product_avg_purchase_prices[product.id] = avg_purchase_price
        total_net_value += avg_purchase_price * product.quantity

    # 计算客户数和客户预存余额汇总
    customers = Customer.query.all()
    total_customers = len(customers)
    total_customer_prepaid = sum(customer.prepaid_amount for customer in customers)

    # 计算供应商数和供应商账户余额汇总
    suppliers = Supplier.query.all()
    total_suppliers = len(suppliers)
    total_supplier_prepaid = sum(supplier.prepaid_amount for supplier in suppliers)

    # 计算库存现金总余额
    last_cash_record = Cash.query.order_by(Cash.id.desc()).first()
    total_cash_balance = last_cash_record.balance if last_cash_record else 0.0

    # 计算所有客户的预存现金总余额
    total_predeposit_balance = get_total_predeposit_balance()

    # 计算总现金余额（库存现金 + 客户预存现金）
    total_cash_and_predeposit_balance = total_cash_balance + total_predeposit_balance

    # 将所有统计数据传递给模板
    return render_template('statistics.html',
                           total_quantity=total_quantity,
                           total_value=total_value,
                           total_products=total_products,
                           products_with_stock=products_with_stock,
                           total_net_value=total_net_value,
                           total_customers=total_customers,
                           total_customer_prepaid=total_customer_prepaid,
                           total_suppliers=total_suppliers,
                           total_supplier_prepaid=total_supplier_prepaid,
                           total_cash_balance=total_cash_balance,
                           total_predeposit_balance=total_predeposit_balance,
                           total_cash_and_predeposit_balance=total_cash_and_predeposit_balance)






if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)

